package quickdb.db.connection;

import java.sql.*;

/**
 *
 * @author Diego Sarmentero
 */
abstract public class ConnectionDB implements IConnectionDB{

    protected Connection connection = null;
    protected Statement statement = null;
    protected ResultSet rs = null;
    protected boolean tryAgain = true;
    //Connection Data
    protected String host;
    protected String port;
    protected String nameDB;
    private String username;
    private String password;
    protected String url;
    protected String driver;
    protected String schema;

    /** Constructs a database connection.
     * Child-classes should:
     * - Call super(dbParameters)
     * - Write the driver class to the driver field
     * - Write the database URL to the url field (use the host port and nameDB fields to generate this)
     * - Call Class.forName(this.driver)
     * 
     * @param dbParameters a variable set of parameters. This depends on which database type, but the following are always
     * expected: host, port, nameDB, username, password
     */
    public ConnectionDB(String... dbParameters) {
        this.host = dbParameters[0];
        this.port = dbParameters[1];
        this.nameDB = dbParameters[2];
        this.username = dbParameters[3];
        this.password = dbParameters[4];
    }

    @Override
    public void connect() throws SQLException {
        connection = DriverManager.getConnection(url, username, password);
        this.connection.setAutoCommit(false);
    }

    @Override
    public void closeConnection() throws SQLException{
        connection.close();
    }

    @Override
    public void openBlock(String tableName) throws SQLException{
        try {
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = statement.executeQuery("SELECT * FROM " + tableName + ";");
            rs.moveToInsertRow();
            tryAgain = true;
        } catch (SQLException e) {
            if (tryAgain) {
                this.connect();
                tryAgain = false;
                this.openBlock(tableName);
            } else {
            	throw e;
            }
        }
    }

    @Override
    public void insertField(String columnName, Object columnValue) throws SQLException {
        try {
            rs.updateObject(columnName, columnValue);
        } catch (SQLException e) {
            throw e;
        } catch (NullPointerException nullEx) {
            SQLException sqlEx = new SQLException();
            throw sqlEx;
        }
    }

    @Override
    public int insertRow(String query) throws SQLException {
        int index = 0;
        try {
            statement = connection.createStatement();
            statement.execute(query + ";");
            rs = statement.getGeneratedKeys();
            rs.next();
            index = rs.getInt(1);
            statement.close();
        } catch (SQLException e) {
            throw e;
        } catch (NullPointerException nullEx) {
            SQLException sqlEx = new SQLException();
            throw sqlEx;
        }

        return index;
    }

    @Override
    public int closeBlock() throws SQLException {
        int index = 0;
        try {
            if (rs != null) {
                rs.insertRow();
                rs.close();
                rs = statement.executeQuery("SELECT LAST_INSERT_ID();");
                rs.next();
                index = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            rs.close();
            statement.close();
        }

        return index;
    }

    @Override
    public boolean existTable(String tableName) throws SQLException {
        statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        try {
        	rs = statement.executeQuery("SELECT * FROM " + tableName + " LIMIT 1;");
        } catch (SQLException e) {
        	// Failure means no table
        	return false;
        }
        return rs.next();
    }

    @Override
    public ResultSet updateField(String table, String where) throws SQLException {
        try {
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = statement.executeQuery("SELECT * FROM " + table + " WHERE " + where + ";");
            tryAgain = true;
        } catch (SQLException e) {
            if (tryAgain) {
                this.connect();
                tryAgain = false;
                return this.updateField(table, where);
            } else {
            	throw e;
            }
        }
        return rs;
    }

    @Override
    public ResultSet select(String select) throws SQLException {
        try {
            statement = connection.createStatement();
            rs = statement.executeQuery(select + ";");
            tryAgain = true;
            return rs;
        } catch (SQLException e) {
            if (tryAgain) {
                this.connect();
                tryAgain = false;
                return this.select(select);
            } else {
                throw e;
            }
        }
    }

    @Override
    public ResultSet selectWhere(String table, String where) throws SQLException {
        try {
            statement = connection.createStatement();
            rs = statement.executeQuery("SELECT * FROM " + table + " WHERE " + where + ";");
            tryAgain = true;
            return rs;
        } catch (SQLException e) {
            if (tryAgain) {
                this.connect();
                tryAgain = false;
                return this.selectWhere(table, where);
            } else {
                throw e;
            }
        }
    }

    @Override
    public Connection getConnection() {
        return this.connection;
    }

    @Override
    public ResultSet getRs() {
        return this.rs;
    }

    @Override
    public void initTransaction() throws SQLException {
        connection.setAutoCommit(false);
    }

    @Override
    public void cancelTransaction() throws SQLException {
        connection.rollback();
    }

    @Override
    public void confirmTransaction() throws SQLException {
        connection.commit();
    }

    @Override
    public void executeQuery(String sql) throws SQLException {
        try {
            statement = connection.createStatement();

            statement.executeUpdate(sql + ";");

            statement.close();
            tryAgain = true;
        } catch (Exception e) {
            if (tryAgain) {
                this.connect();
                tryAgain = false;
                this.executeQuery(sql);
            } else {
                throw new SQLException(e);
            }
        }
    }

    @Override
    public void disconnect() throws SQLException {
            connection.close();
    }

    @Override
    public void deleteRows(String tableName, String where) throws SQLException {
        // Creamos una statement SQL
        statement = connection.createStatement();

        // Ejecutamos la consulta
        statement.executeUpdate("DELETE FROM " + tableName + " WHERE " + where + ";");

        statement.close();
    }

    @Override
    public String getSchema(){
        return this.schema;
    }

}
